[Snowflake] 半構造化データ用の関数を色々使ってみた

感情論で語る半構造　それでは所詮は敢闘賞

モダンデータスタック(MDS)

#Snowflake

#JSON

たまちゃん

2021.06.24

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

大阪オフィスの玉井です。

下記のウェビナーを聴講したのですが（レポートも書いた）、Snowflakeの半構造化データ用のクエリや関数を試すのに丁度いいリソースが紹介されていました。

この記事では、それを使いつつ、半構造化データ用の関数をご紹介します。

準備

使用するリソース

やっておくこと

上記のGetting Startedを済ませておきます。

使用するデータ

準備を終えると、下記のテーブルが用意されます。

見ればわかる通り、JSONファイルがVARIANT型として、そのまま格納されています。JSONファイルの中身については、上記のリポジトリで確認することができます。

半構造化データ用の関数いろいろ

FLATTEN

Snowflake上の半構造化データの処理において、よく使用する関数です。

複合値を複数の行にフラット化（展開）します。

その名の通り、JSON等の半構造化データを、行列の形に展開する関数です。といっても、文章だけだとわかりづらいため、実際に関数を実行してみます。

例えば、事前に用意したテーブルに対して、下記のクエリを発行してみます。

SELECT
    *
FROM
    raw_device_data,
    LATERAL FLATTEN(INPUT => raw:Views)
;

結果は下記の通り。INPUTに指定した部分以下をバラしてくれます。今回指定しているのはARRAYです（後述）。

最初は、クエリ結果を見ても、ピンと来ないと思います。JSONを可視化してくれるツールで、JSONファイル自体と見比べると、わかりやすいです。

例えば、元々のテーブルのRAWの1行目は、下記のような構造になっています（わかりやすいように、最後まで展開していません）。

このJSONは、Viewsという配列が1個あり、そこに各オブジェクトがズラズラと並んでいる構造をしています。先程のクエリはINPUTにraw:Viewsと指定しているため、各配列の要素毎に行となってバラける結果となっています。要素毎にはINDEXがつけられ、VALUEというカラムには、配列の各オブジェクトがそれぞれ入ります。

ちなみに、FLATTENの前に記述しているLATERALとは何でしょうか。これはLATERAL結合といって、raw_device_dataの各行毎に、FLATTEN関数をかけ、その結果を結合しています（だからクエリ結果には、raw_device_dataの3カラムも存在する）。

詳しくは、下記の記事がめちゃくちゃわかりやすいので、一読してみてください。

OBJECT_KEYS

FLATTENを使って、各オブジェクト毎に行を分けることはできましたが、各オブジェクトは、まだそれぞれが半構造な形を保っており、このまま分析に使用するのは難しいです。

オブジェクトの値を取る方法は色々ありますが、今回はOBJECT_KEYSを紹介します。

入力オブジェクトのキーのリストを含む配列を返します。

早速、下記のクエリを実行してみます。

SELECT
    filename,
    "FLATTENしたやつ".INDEX AS "INDEX",
    "FLATTENしたやつ".VALUE AS "オブジェクト",
    OBJECT_KEYS("オブジェクト") AS "オブジェクトのKey",
    "オブジェクトのKey"[0]::string AS "純粋な値"
FROM
    raw_device_data,
    LATERAL FLATTEN(INPUT => raw:Views) "FLATTENしたやつ"
;

結果は下記の通り。「純粋な値」カラムに、最終的にバラしきった値が入っています。

OBJECT_KEYSは、その名の通り、指定したオブジェクトのキーを取得する関数です。FLATTENでバラした各オブジェクトを、OBJECT_KEYSに指定することで、バラした各オブジェクトのキーを取得することができます。ドキュメントに書いてますが、返り値は配列なので、[0]::stringというような指定をすれば、余計な記号（カギカッコなど）を除去した、純粋な値（今回はstringを指定）を取り出すことができます。

GET

各オブジェクトのキーが取得できたら、今度はオブジェクトの値もとりたいですよね。ということで、GET関数を使ってみます。

オブジェクトまたは配列から値を抽出します。

下記のクエリを実行してみます。

SELECT
    filename,
    "FLATTENしたやつ".INDEX AS "INDEX",
    "FLATTENしたやつ".VALUE AS "オブジェクト",
    OBJECT_KEYS("オブジェクト") AS "オブジェクトのKey",
    "オブジェクトのKey"[0]::string AS "純粋な値",
    GET("オブジェクト", "純粋な値") AS "GETしたオブジェクト",
    "GETしたオブジェクト":EnterView::string AS enter_view,
    "GETしたオブジェクト":ExitView::string AS exit_view
FROM
    raw_device_data,
    LATERAL FLATTEN(INPUT => raw:Views) "FLATTENしたやつ"
;

GET関数はオブジェクトから値を取り出すことができますが、引数としてフィールドを指定します。今回はオブジェクトのキー（OBJECT_KEYSで取り出した直後のものではなく、stringに変換したもの）を指定しているので、そのキー以下のオブジェクトを取り出すことができました。

ここまでバラすことができれば、後は"GETしたオブジェクト":EnterView::stringという指定の仕方で、それぞれの値を（SQLとして処理できる形で）取り出すことができます。

ARRAY_AGG

ARRAYにピボットされた入力値を返します。

これも、先に結果を見たほうがわかりやすいです。

WITH UI_VIEWS AS(
    SELECT
        filename,
        "FLATTENしたやつ".INDEX AS "INDEX",
        "FLATTENしたやつ".VALUE AS "オブジェクト",
        OBJECT_KEYS("オブジェクト") AS "オブジェクトのKey",
        "オブジェクトのKey"[0]::string AS "純粋な値",
        GET("オブジェクト", "純粋な値") AS "GETしたオブジェクト",
        "GETしたオブジェクト":EnterView::string AS enter_view,
        "GETしたオブジェクト":ExitView::string AS exit_view
    FROM
        raw_device_data,
        LATERAL FLATTEN(INPUT => raw:Views) "FLATTENしたやつ"
)

SELECT
    filename,
    ARRAY_AGG("純粋な値") AS "キーのリスト"
FROM
    UI_VIEWS
GROUP BY
    filename
;

今までは半構造な形のデータをバラすのが主目的でしたが、この関数は逆のイメージを想像するとわかりやすいかもしれません。OBJECT_KEYSを使って取り出したオブジェクトのキーを、ARRAY_AGGに引数として渡すと、そのキー（カラム）の値を全て取得して、配列にピボットして返してくれます。

シンプルな配列にできるので、下記のように、その配列から、さらに取り出したい値を狙い撃ちして取り出すこともできます。

-- withは省略

SELECT
    filename,
    ARRAY_AGG("純粋な値") AS "キーのリスト",
    "キーのリスト"[4]::string
FROM
    UI_VIEWS
GROUP BY
    filename
;

OBJECT_AGG

グループごとに1つの OBJECT を返します。

例のごとく、まずは実例をば。

-- withは省略

SELECT
    filename,
    OBJECT_AGG(
        "INDEX"::string,
        "純粋な値"::variant
    ) AS "IDXとキー"
FROM
    UI_VIEWS
GROUP BY
    filename
;